package com.hewep.support;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.hewep.kit.CacheKit;
import com.hewep.kit.ConfigKit;
import com.hewep.support.excel.AbstractExcel2007Writer;
import com.hewep.support.excel.Excel2003Reader;
import com.hewep.support.excel.Excel2007Reader;
import com.hewep.support.excel.Excel2007WriterImpl;
import com.hewep.support.excel.IRowReader;
import com.jfinal.kit.JsonKit;
import com.jfinal.plugin.activerecord.Record;

public class PoiKit {
	
	//excel2003扩展名  
    public static final String EXCEL03_EXTENSION = ".xls";  
    //excel2007扩展名  
    public static final String EXCEL07_EXTENSION = ".xlsx";  
      
    /** 
     * 读取Excel文件，可能是03也可能是07版本 
     * @param excel03 
     * @param excel07 
     * @param fileName 
     * @throws Exception  
     */  
    public static void readExcel(IRowReader reader,String fileName) throws Exception{  
        // 处理excel2003文件  
        if (fileName.endsWith(EXCEL03_EXTENSION)){  
            Excel2003Reader excel03 = new Excel2003Reader();  
            excel03.setRowReader(reader);  
            excel03.process(fileName);  
        // 处理excel2007文件  
        } else if (fileName.endsWith(EXCEL07_EXTENSION)){  
            Excel2007Reader excel07 = new Excel2007Reader();  
            excel07.setRowReader(reader);  
            excel07.process(fileName);  
        } else {  
            throw new Exception("文件格式错误，fileName的扩展名只能是xls或xlsx。");  
        }  
    } 
    
    public static void writeExcel(String fileName,String[] columns, List<Record> records) throws Exception{
    	
    	 //构建excel2007写入器  
        AbstractExcel2007Writer excel07Writer = new Excel2007WriterImpl(columns, records);  
        //调用处理方法  
        excel07Writer.process(fileName);  
    }
    
    public static void writeDatas(String fileName,String[] columns, List<Record> records) throws IOException{
    	//创建工作文档对象     
        Workbook wb = new XSSFWorkbook();    
        //创建sheet对象     
        Sheet sheet1 = (Sheet) wb.createSheet("sheet1");  
        
        //标题
        Map<String, String> columnRemark = CacheKit.getColumnRemark("org");
        Row row = (Row) sheet1.createRow(0);
        for (int i=0; i<columns.length; i++) {
        	Cell cell = row.createCell(i); 
        	cell.setCellValue(columnRemark.get(columns[i])); 
		}
        String[] chars = ConfigKit.getStr("excel_char", "").split(",");
        //循环写入行数据     
        for (int i = 0; i < records.size(); i++) {
        	Record record = records.get(i);
            Row row0 = (Row) sheet1.createRow(i+1);    
            //循环写入列数据     
            for (int j=0; j<columns.length; j++) {
            	String value = "";
            	Object object = record.get(columns[j]);
            	value =  object.toString();
            	for (String char0 : chars) {
            		value = value.replace(char0, "").replace("（", "(").replace("）", ")");
				}
            	Cell cell = row0.createCell(j); 
            	cell.setCellValue(value); 
    		} 
        }    
        //创建文件流     
        OutputStream stream = new FileOutputStream(fileName);    
        //写入数据     
        wb.write(stream);    
        //关闭文件流     
        stream.close();   
        wb.close();
    }
    /**
     * 获取 注释字段对应关系 
     * @param file
     * @return
     */
	public static String readForJson(File file) {
		Map<String, String> remarkColumn = new HashMap<String, String>();
		Map<String, String> columnRemark = new HashMap<String, String>();
		try {
			XSSFWorkbook wookbook = new XSSFWorkbook(new FileInputStream(file));
			XSSFSheet sheet = wookbook.getSheet("Sheet1");
			
			XSSFRow header = sheet.getRow(0);
			XSSFRow body = sheet.getRow(1);
			int len = header.getPhysicalNumberOfCells();
			for (int i = 0; i < len; i++) {
				String key = header.getCell(i).getStringCellValue().trim();
				String value = body.getCell(i).getStringCellValue().trim();
				remarkColumn.put(key, value);
				columnRemark.put(value, key);
			}
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return JsonKit.toJson(columnRemark)+"|"+JsonKit.toJson(remarkColumn);
	}
	
	/**
	 * 根据 指定的表 获取  List 数据 
	 * @param tableName
	 * @param file
	 * @return
	 */
	@SuppressWarnings("resource")
	public static List<Record> readDatas(String tableName, File file) {
		List<Record> records = new ArrayList<Record>();String temp = "";
		try {
			XSSFWorkbook wookbook = new XSSFWorkbook(new BufferedInputStream(new FileInputStream(file)));
			XSSFSheet sheet = wookbook.getSheet("Sheet1");
			// 表头
			XSSFRow header = sheet.getRow(0);
			int rows = sheet.getPhysicalNumberOfRows();
			// 表体
			for (int i = 1; i < rows; i++) {
				Record record = new Record();
				XSSFRow row = sheet.getRow(i);
				if (row != null) {
					int cells = row.getPhysicalNumberOfCells();
					for (int j = 0; j < cells; j++) {
						String name = header.getCell(j).getStringCellValue().trim();
						temp = name;
						name = CacheKit.getColumnByRemark(tableName, name);
						XSSFCell cell = row.getCell(j);
						if (cell != null) {
							record.set(name, cell.getStringCellValue());
						}else{
							record.set(name, "");
						}
					}
				}
				records.add(record);
			}
			wookbook = null;
		} catch (Exception e) {
			System.out.println(temp);
			e.printStackTrace();
		}
		return records;
	}
	
}
